package com.anolesoft.secondaryCostAccounting.mapper;


import cn.hutool.core.util.StrUtil;
import com.anolesoft.secondaryCostAccounting.dto.EmployeeFromSCADTO;
import com.anolesoft.secondaryCostAccounting.dto.MatchInfoDTO;
import com.anolesoft.secondaryCostAccounting.dto.SCAccountingDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

@Mapper
public interface SqlserverMatchServiceMapper {

    /**
     * 查询sql
     * @param matchInfoDTO  查询条件
     * @param num           查询次数 只保留3
     *                      0 材料牌号、材料规格、计量单位、机型、派工号、当前库存大于等于实发数量
     *                      1 材料牌号、材料规格、计量单位、机型、当前库存大于等于实发数量
     *                      2 材料牌号、材料规格、计量单位、当前库存大于等于实发数量
     *                      3 材料牌号、材料规格、计量单位、当前库存大于0
     * @param likeGuiGe     规格是否模糊查询
     * @param likeDanWei    计量单位是否模糊查询 已弃用
     * @return
     */
    @SelectProvider(type = createSQL.class, method = "matchSCASQL")
    //List<SCAccountingDTO> matchSCA(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO, int num, boolean likeGuiGe, boolean likeDanWei);
    List<SCAccountingDTO> matchSCA(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO, boolean likeGuiGe);
    public static class createSQL {
        //public static String matchSCASQL(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO, int num, boolean likeGuiGe, boolean likeDanWei){
        public static String matchSCASQL(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO, boolean likeGuiGe){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" 器材编码 as equipid, 材料名称 as equipname, 类别 as category, 材料牌号 as materialMark,");
                    sql.append(" 技术标准 as materialStd, 材料规格 as materialFmt, 计量单位 as unit, 用途项目 as purpose,");
                    sql.append(" 到货登记号 as registerId, LPH as lupihao, 存放库位 as location, 结存数量 as stock, 结存金额 as jcAmount,");
                    sql.append(" 核算价格 as accountPrice, '0.00' as sfAmount, 派工号 as lrReturnPgh, ");
                    sql.append(" 产品代码 as jx, CONVERT(varchar(100), 日期, 23) as daohuotime,ZY_NO as zhangye from dbo.标准件中心库存");
                    sql.append(" where REPLACE(材料牌号,' ','') = REPLACE(#{matchInfoDTO.materialMark, jdbcType=VARCHAR},' ','')");
                    //sql.append(" and 技术标准 = #{matchInfoDTO.materialStd, jdbcType=VARCHAR}");
                    if (likeGuiGe) {
                        sql.append(" and 材料规格 like #{matchInfoDTO.materialFmt, jdbcType=VARCHAR} + '%'");
                    } else {
                        sql.append(" and REPLACE(材料规格,'×','X') = REPLACE(#{matchInfoDTO.materialFmt, jdbcType=VARCHAR},'×','X')");
                    }
                    //if (likeDanWei) {
                    //    sql.append(" and 计量单位 in (");
                    //    sql.append(matchInfoDTO.getUnit());
                    //    sql.append(")");
                    //} else {
                    //    sql.append(" and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR}");
                    //}
                    //switch (num) {
                    //    case 0: sql.append(" and 派工号 = #{matchInfoDTO.lrReturnPgh, jdbcType=VARCHAR}");
                    //    case 1: sql.append(" and 产品代码 = #{matchInfoDTO.jx, jdbcType=VARCHAR}");
                    //    case 2: sql.append(" and 结存数量 >= #{matchInfoDTO.issueNum, jdbcType=VARCHAR}");
                    //    break;
                    //    default: sql.append(" and 结存数量 > 0");
                    //}
                    sql.append(" and 结存数量 > 0");
                    sql.append(" and 单位代码 = '01400'");
                    //sql.append(" and 结存数量 > 0");
                    //if (!StrUtil.isEmptyOrUndefined(matchInfoDTO.getLupihao())){
                    //    sql.append(" and LPH = #{matchInfoDTO.lupihao, jdbcType=VARCHAR} ");
                    //}
                    sql.append(" order by 日期");
                    SELECT(sql.toString());
                    System.out.println("matchSCASQL:" + sql.toString());
                }
            }.toString();
        }
    }

    /**
     * 获取匹配数据数量 已弃用
     * @param matchInfoDTO  查询条件
     * @param likeGuiGe     材料规格是否模糊查询
     * @param likeDanWei    计量单位是否模糊查询
     * @return
     */
    //@Select(" select count(*) from dbo.标准件中心库存 " +
    //        "  where REPLACE(材料牌号,' ','') = #{matchInfoDTO.materialMark, jdbcType=VARCHAR} " +
    //        "  and 技术标准 = #{matchInfoDTO.materialStd, jdbcType=VARCHAR} " +
    //        "  and 材料规格 = #{matchInfoDTO.materialFmt, jdbcType=VARCHAR} " +
    //        "  and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR} " +
    //        //"  and LPH = #{matchInfoDTO.lupihao, jdbcType=VARCHAR} " +
    //        "  and 单位代码 like '%1400'" +
    //        "  and 结存数量 > 0")
    @SelectProvider(type = createSQL2.class, method = "matchNumSQL")
    int matchNum(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO, boolean likeGuiGe, boolean likeDanWei);
    public static class createSQL2 {
        public static String matchNumSQL(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO, boolean likeGuiGe, boolean likeDanWei){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" count(*) from dbo.标准件中心库存");
                    sql.append(" where REPLACE(材料牌号,' ','') = REPLACE(#{matchInfoDTO.materialMark, jdbcType=VARCHAR},' ','')");
                    //sql.append(" and 技术标准 = #{matchInfoDTO.materialStd, jdbcType=VARCHAR}");
                    //sql.append(" and 材料规格 = #{matchInfoDTO.materialFmt, jdbcType=VARCHAR}");
                    //sql.append(" and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR}");
                    if (likeGuiGe) {
                        sql.append(" and 材料规格 like #{matchInfoDTO.materialFmt, jdbcType =VARCHAR} + '%'");
                    } else {
                        sql.append(" and REPLACE(材料规格,'×','X') = REPLACE(#{matchInfoDTO.materialFmt, jdbcType=VARCHAR},'×','X')");
                    }
                    if (likeDanWei) {
                        sql.append(" and 计量单位 in (");
                        sql.append(matchInfoDTO.getUnit());
                        sql.append(")");
                    } else {
                        sql.append(" and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR}");
                    }
                    sql.append(" and 结存数量 > 0");
                    sql.append(" and 单位代码 like '%1400'");
                    //if (!StrUtil.isEmptyOrUndefined(matchInfoDTO.getLupihao())){
                    //    sql.append(" and LPH = #{matchInfoDTO.lupihao, jdbcType=VARCHAR} ");
                    //}
                    SELECT(sql.toString());
                    System.out.println("matchNumSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    @Select("select 工段 as gongduan, 班组 as banzu from dbo.人员状况定义" +
            " where 机构代码 = '01400'" +
            " and 人员编码 = #{eid, jdbcType=VARCHAR}")
    EmployeeFromSCADTO selectGdBz(String eid);

    //查询匹配数据1 材料牌号、技术标准、材料规格、计量单位、机型、派工号、当前库存大于等于实发数量
    @Select(" select 器材编码 as equipid, 材料名称 as equipname, 类别 as category, 材料牌号 as materialMark, " +
            "技术标准 as materialStd, 材料规格 as materialFmt, 计量单位 as unit, 用途项目 as purpose, " +
            "到货登记号 as registerId, LPH as lupihao, 存放库位 as location, 结存数量 as stock, 结存金额 as jcAmount, " +
            "核算价格 as accountPrice, 发出金额 as sfAmount, 派工号 as lrReturnPgh, " +
            "产品代码 as jx, CONVERT(varchar(100), 日期, 23) as daohuotime from dbo.标准件中心库存 " +
            "  where REPLACE(材料牌号,' ','') = #{matchInfoDTO.materialMark, jdbcType=VARCHAR} " +
            "  and 技术标准 = #{matchInfoDTO.materialStd, jdbcType=VARCHAR} " +
            "  and 材料规格 = #{matchInfoDTO.materialFmt, jdbcType=VARCHAR} " +
            "  and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR} " +
            //"  and LPH = #{matchInfoDTO.lupihao, jdbcType=VARCHAR} " +
            "  and 单位代码 like '%1400'" +
            "  and 产品代码 = #{matchInfoDTO.jx, jdbcType=VARCHAR}" +
            "  and 派工号 = #{matchInfoDTO.lrReturnPgh, jdbcType=VARCHAR}" +
            "  and 结存数量 >= #{matchInfoDTO.issueNum, jdbcType=VARCHAR}" +
            "  order by 日期")
    List<SCAccountingDTO> matchSCA1(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO);

    //查询匹配数据2 材料牌号、技术标准、材料规格、计量单位、机型、当前库存大于等于实发数量
    @Select(" select 器材编码 as equipid, 材料名称 as equipname, 类别 as category, 材料牌号 as materialMark, " +
            "技术标准 as materialStd, 材料规格 as materialFmt, 计量单位 as unit, 用途项目 as purpose, " +
            "到货登记号 as registerId, LPH as lupihao, 存放库位 as location, 结存数量 as stock, 结存金额 as jcAmount, " +
            "核算价格 as accountPrice, 发出金额 as sfAmount, 派工号 as lrReturnPgh, " +
            "产品代码 as jx, CONVERT(varchar(100), 日期, 23) as daohuotime from dbo.标准件中心库存 " +
            "  where REPLACE(材料牌号,' ','') = #{matchInfoDTO.materialMark, jdbcType=VARCHAR} " +
            "  and 技术标准 = #{matchInfoDTO.materialStd, jdbcType=VARCHAR} " +
            "  and 材料规格 = #{matchInfoDTO.materialFmt, jdbcType=VARCHAR} " +
            "  and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR} " +
            //"  and LPH = #{matchInfoDTO.lupihao, jdbcType=VARCHAR} " +
            "  and 单位代码 like '%1400'" +
            "  and 产品代码 = #{matchInfoDTO.jx, jdbcType=VARCHAR}" +
            //"  and 派工号 = #{matchInfoDTO.lrReturnPgh, jdbcType=VARCHAR}" +
            "  and 结存数量 >= #{matchInfoDTO.issueNum, jdbcType=VARCHAR}" +
            "  order by 日期")
    List<SCAccountingDTO> matchSCA2(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO);

    //查询匹配数据3 材料牌号、技术标准、材料规格、计量单位、当前库存大于等于实发数量
    @Select(" select 器材编码 as equipid, 材料名称 as equipname, 类别 as category, 材料牌号 as materialMark, " +
            "技术标准 as materialStd, 材料规格 as materialFmt, 计量单位 as unit, 用途项目 as purpose, " +
            "到货登记号 as registerId, LPH as lupihao, 存放库位 as location, 结存数量 as stock, 结存金额 as jcAmount, " +
            "核算价格 as accountPrice, 发出金额 as sfAmount, 派工号 as lrReturnPgh, " +
            "产品代码 as jx, CONVERT(varchar(100), 日期, 23) as daohuotime from dbo.标准件中心库存 " +
            "  where REPLACE(材料牌号,' ','') = #{matchInfoDTO.materialMark, jdbcType=VARCHAR} " +
            "  and 技术标准 = #{matchInfoDTO.materialStd, jdbcType=VARCHAR} " +
            "  and 材料规格 = #{matchInfoDTO.materialFmt, jdbcType=VARCHAR} " +
            "  and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR} " +
            //"  and LPH = #{matchInfoDTO.lupihao, jdbcType=VARCHAR} " +
            "  and 单位代码 like '%1400'" +
            //"  and 产品代码 = #{matchInfoDTO.jx, jdbcType=VARCHAR}" +
            //"  and 派工号 = #{matchInfoDTO.lrReturnPgh, jdbcType=VARCHAR}" +
            "  and 结存数量 >= #{matchInfoDTO.issueNum, jdbcType=VARCHAR}" +
            "  order by 日期")
    List<SCAccountingDTO> matchSCA3(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO);

    //查询匹配数据4 材料牌号、技术标准、材料规格、计量单位
    @Select(" select 器材编码 as equipid, 材料名称 as equipname, 类别 as category, 材料牌号 as materialMark, " +
            "技术标准 as materialStd, 材料规格 as materialFmt, 计量单位 as unit, 用途项目 as purpose, " +
            "到货登记号 as registerId, LPH as lupihao, 存放库位 as location, 结存数量 as stock, 结存金额 as jcAmount, " +
            "核算价格 as accountPrice, 发出金额 as sfAmount, 派工号 as lrReturnPgh, " +
            "产品代码 as jx, CONVERT(varchar(100), 日期, 23) as daohuotime from dbo.标准件中心库存 " +
            "  where REPLACE(材料牌号,' ','') = #{matchInfoDTO.materialMark, jdbcType=VARCHAR} " +
            "  and 技术标准 = #{matchInfoDTO.materialStd, jdbcType=VARCHAR} " +
            "  and 材料规格 = #{matchInfoDTO.materialFmt, jdbcType=VARCHAR} " +
            "  and 计量单位 = #{matchInfoDTO.unit, jdbcType=VARCHAR} " +
            //"  and LPH = #{matchInfoDTO.lupihao, jdbcType=VARCHAR} " +
            "  and 单位代码 like '%1400'" +
            //"  and 产品代码 = #{matchInfoDTO.jx, jdbcType=VARCHAR}" +
            //"  and 派工号 = #{matchInfoDTO.lrReturnPgh, jdbcType=VARCHAR}" +
            "  and 结存数量 > 0" +
            "  order by 日期")
    List<SCAccountingDTO> matchSCA4(@Param("matchInfoDTO") MatchInfoDTO matchInfoDTO);
}